package at.fhj.itm10.ticketmaster.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import at.fhj.itm10.ticketmaster.models.Event;

/**
 * Event DAO.
 * 
 * @author Benjamin Hirt
 */
public class EventDAO extends AbstractDAO {
	
	/**
	 * Must be an integer in the database.
	 */
	private final String PRIMARYKEY_NAME = "id";

	private final String SQL_INSERT ="INSERT INTO `event`(`name`, `date_time`, `primeevent`, `ticket_price`, `ticket_amount`, " +
			"`event_canceled`, `description`, `enter`, `begin`, `flyer`, `fk_address_id`, `fk_customer_id`, `fk_eventtype_id`) " +
			"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	private final String SQL_UPDATE = "UPDATE `event` SET `name` = ?, `date_time` = ?, `primeevent` = ?, `ticket_price` = ?, `ticket_amount` = ?," +
			" `event_canceled` = ?, `description` = ?, `enter` = ?, `begin` = ?, `flyer` = ?, `fk_address_id` = ?, `fk_customer_id` = ?," +
			" `fk_eventtype_id` = ? WHERE `id` = ?";
	
	private final String SQL_DELETE = "DELETE FROM `event` WHERE `id` = ?";
	
	private final String SQL_FINDBYID = "SELECT * FROM `event` WHERE `id` = ?";
	
	private final String SQL_FINDALL = "SELECT * FROM `event`";

	private final String SQL_FINDCATEGORY = "SELECT * FROM `event` WHERE `event`.`fk_eventtype_id` = ?";
	
	private final String SQL_FINDDATE = "SELECT * FROM `event` WHERE `date_time` BETWEEN ? AND ?";
	
	private final String SQL_FINDADDRESS =  "SELECT * FROM `event` WHERE `event`.`fk_address_id` =?";
	
	private final String SQL_FINDNAME =  "SELECT * FROM `event` WHERE `event`.`name` like ?";
	
	
	public EventDAO(Connection connection) throws DAOException {
		super(connection);
	}

	public void insert(Event entity) throws DAOException {
		checkForValidEntity(entity);
		PreparedStatement statement = prepareStatement(SQL_INSERT);
		
		try {
			statement.setString(1,entity.getName());
			statement.setString(2,entity.getDateTime());
			statement.setInt(3,entity.getPrimeevent());
			statement.setDouble(4,entity.getTicketPrice());
			statement.setInt(5,entity.getTicketAmount());
			statement.setInt(6,entity.getEventCanceled());
			statement.setString(7,entity.getDescription());
			statement.setString(8,entity.getEnter());
			statement.setString(9,entity.getBegin());
			statement.setString(10,entity.getFlyer());
			statement.setLong(11,entity.getFkAddressId());
			statement.setInt(12,entity.getFkCustomerId());
			statement.setInt(13,entity.getFkEventtypeId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		super.processInsert(statement, entity);
	}

	public void update(Event entity) throws DAOException {
		checkForValidEntity(entity);
		PreparedStatement statement = prepareStatement(SQL_UPDATE);
		
		try {
			statement.setString(1,entity.getName());
			statement.setString(2,entity.getDateTime());
			statement.setInt(3,entity.getPrimeevent());
			statement.setDouble(4,entity.getTicketPrice());
			statement.setInt(5,entity.getTicketAmount());
			statement.setInt(6,entity.getEventCanceled());
			statement.setString(7,entity.getDescription());
			statement.setString(8,entity.getEnter());
			statement.setString(9,entity.getBegin());
			statement.setString(10,entity.getFlyer());
			statement.setLong(11,entity.getFkAddressId());
			statement.setInt(12,entity.getFkCustomerId());
			statement.setInt(13,entity.getFkEventtypeId());
			statement.setLong(14, entity.getId());
		} catch (SQLException e) {
			throw new DAOException(e);
		}
		
		super.processUpdate(statement, entity);
	}

	public void delete(Event entity) throws DAOException {
		checkForValidEntity(entity);

		super.doDelete(prepareStatement(SQL_DELETE), entity);
	}

	public Event findById(long id) throws DAOException {
		Event entity = (Event) retrieveEntity(id);
		if (entity != null) {
			return entity;
		}

		return (Event) super.doFindOne(id, prepareStatement(SQL_FINDBYID));
	}

	public List<Event> findAll() throws DAOException {
		return super.doFindMore(PRIMARYKEY_NAME, prepareStatement(SQL_FINDALL));
	}

	/**
	 * {@inheritDoc}
	 */
	protected Event fillEntityByResult(ResultSet resultSet) {
		Event event = new Event();
		
		try {
			event.setId(resultSet.getLong(PRIMARYKEY_NAME));
			event.setName(resultSet.getString("name"));
			event.setDateTime(resultSet.getString("date_time"));
			event.setPrimeevent(resultSet.getInt("primeevent"));
			event.setTicketPrice(resultSet.getDouble("ticket_price"));
			event.setTicketAmount(resultSet.getInt("ticket_amount"));
			event.setEventCanceled(resultSet.getInt("event_canceled"));
			event.setDescription(resultSet.getString("description"));
			event.setEnter(resultSet.getString("enter"));
			event.setBegin(resultSet.getString("begin"));
			event.setFlyer(resultSet.getString("flyer"));
			event.setFkAddressId(resultSet.getInt("fk_address_id"));
			event.setFkCustomerId(resultSet.getInt("fk_customer_id"));
			event.setFkEventtypeId(resultSet.getInt("fk_eventtype_id"));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return event;
	}
	
	/*
	 * normal Search
	 * Author: Christopher Barilich
	 * 
	 */
	
	public List<Event> findByName(String eventname) throws DAOException, SQLException {
		
		PreparedStatement statement = prepareStatement(SQL_FINDNAME);
		statement.setString(1, "%"+eventname+"%"); //add % wildcards around serchterm, not in query for prepared statemend 
		return super.doFindMore(PRIMARYKEY_NAME, statement); 
	}
	
	
	/*
	 * Extended Search
	 * Author: Gerald Hambrosch
	 * 
	 */
	
	public List<Event> findByCategory(long c_id) throws DAOException, SQLException {
		PreparedStatement statement = prepareStatement(SQL_FINDCATEGORY);
		statement.setLong(1, c_id); //add Parameter (for each "?") to prepared statement
		return super.doFindMore(PRIMARYKEY_NAME, statement); 
	}

	public List<Event> findByDate(String start, String end) throws DAOException, SQLException {
		PreparedStatement statement = prepareStatement(SQL_FINDDATE);
		statement.setString(1, start);
		statement.setString(2, end);
		return super.doFindMore(PRIMARYKEY_NAME, statement);
	}
	
	public List<Event> findByAddress(long id) throws SQLException, DAOException{
		System.out.println("gestartet");
		PreparedStatement statement = prepareStatement(SQL_FINDADDRESS);
		statement.setLong(1, id);
		return super.doFindMore(PRIMARYKEY_NAME, statement);
		
	}
}
